﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlTypes;


namespace DATN.mds.ContactManagement
{
    public partial class GroupIndex : System.Web.UI.Page
    {
        private string cnnString;
        SqlConnection cnn;
        SqlCommand cmd;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Session["Us_Id"] != null)
                {
                    if (Session["AllGroupID"] != null)
                    {
                        //Load Dropdownlist:
                        BindDrr();

                        //Hiên thị GridView:
                        BindDatatoGridView();
                    }
                    else {
                      //  lbName.Text = "Đừng cheat URL.";
                    }
                }
                else
                {
                    Response.Redirect("../Account/Login.aspx");
                }
                Validate();
            }
        }

   

        //Bind data to drr
        protected void BindDrr()
        {
            try
            {
                cnnString = ConfigurationManager.ConnectionStrings["DATNconnection"].ToString();
                cnn = new SqlConnection(cnnString);
                cnn.Open();

                string SearchQuery = "SELECT * FROM dbo.GroupCustomer WHERE Us_Id = @Us_Id";

                cmd = new SqlCommand(SearchQuery, cnn);

                cmd.Parameters.Add("@Us_Id", SqlDbType.Int);
                cmd.Parameters["@Us_Id"].Value = Convert.ToInt16(Session["Us_Id"]);

                SqlDataReader reader = cmd.ExecuteReader();

                //Group Khach hang:
                var GroupCS = new Dictionary<string, string>();
                GroupCS.Add("-1", "Tất Cả");

                int i = 0;
                while (reader.Read())
                    {
                        i++;
                        GroupCS.Add(reader["GrCus_Id"].ToString(), reader["Name"].ToString());
                    }

                    Drr_Group.DataTextField = "Value";
                    Drr_Group.DataValueField = "Key";
                    Drr_Group.DataSource = GroupCS;
                    Drr_Group.DataBind();
                    
                if (i == 0)
                    {
                        lb_AddtoGroup.Text = "B?n Chua có Nhóm Khách Hàng!<br/> Click vào T?o Nhóm Ð? T?o Nhóm khách Hàng";
                        Drr_Group.Visible = false;
                        btnAddtoGroup.Visible = false;
                    }
                    else
                    {
                        Drr_Group.Visible = true;
                        btnAddtoGroup.Visible = true;
                        lb_AddtoGroup.Text = "";
                    }
               
                cnn.Close();
            }
            catch (Exception ex)
            {
                cnn.Close();
                Response.Write("Lỗi: " + ex);
            }
            finally
            {
                cnn.Close();
            }
        }


        //Tạo vào nhóm GroupCS
        protected void AddtoGroupCs()
        {
            try
            {
                lb_AddtoGroup.Text = "";
                string a = Session["AllGroupID"].ToString();
                Session.Contents.Remove("AllGroupID");
                string[] words = a.Split(',');

                
                for (int i = 0; i < (words.Length - 1); i++)
                {
                   //lb_Status.Text += words[i].ToString() + us_id.ToString();
                   cnnString = ConfigurationManager.ConnectionStrings["DATNconnection"].ToString();
                   cnn = new SqlConnection(cnnString);
                   cnn.Open();
                   cmd = cnn.CreateCommand();
                   cmd.CommandText = "INSERT INTO [dbo].[GroupCs] ([GrCus_Id], [Cus_Id])"
                                    + "VALUES (@GrCus_Id, @Cus_Id)";

                   cmd.Parameters.Add("@GrCus_Id", SqlDbType.Int);
                   cmd.Parameters["@GrCus_Id"].Value = Convert.ToInt16(Drr_Group.SelectedValue);

                   cmd.Parameters.Add("@Cus_Id", SqlDbType.Int);
                   cmd.Parameters["@Cus_Id"].Value = Convert.ToInt16(words[i]);

                   cmd.ExecuteNonQuery();
                   cnn.Close();
                }
                lb_AddtoGroup.Text = "Cập nhật thành công!";
            }catch (Exception ex)
            {
                lb_AddtoGroup.Text = "Lỗi ở nhập GroupCs" + ex;
                cnn.Close();
            }
        }

        //Check Customer đấy đã add vào chưa
        int CkCus;
        protected int CheckCus(string Cus_id)
        {
            CkCus = 0;
            int cus_id = Convert.ToInt16(Cus_id);

            try
            {
                string cnnString = ConfigurationManager.ConnectionStrings["DATNconnection"].ConnectionString;
                cnn = new SqlConnection(cnnString);
                cnn.Open();

                String queryString = "SELECT Count(*) FROM GroupCs WHERE GrCus_Id = @GrCus_Id AND Cus_Id = @Cus_Id";
                cmd = new SqlCommand(queryString, cnn);

                cmd.Parameters.Add("@GrCus_Id", SqlDbType.Int);
                cmd.Parameters["@GrCus_Id"].Value = Convert.ToInt16(Drr_Group.SelectedValue);

                cmd.Parameters.Add("@Cus_Id", SqlDbType.Int);
                cmd.Parameters["@Cus_Id"].Value = cus_id;

                CkCus = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                cnn.Close();
            }
            catch (Exception ex)
            {
                cnn.Close();
                Response.Write(" Lỗi ở Check Mail: " + ex);
            }
            return CkCus;
        }


        //Bind Data vào Gridview:
        protected void BindDatatoGridView()
        { 
            gvCheckboxes.DataSource = null;
            gvCheckboxes.DataBind();

            string a = Session["AllGroupID"].ToString();
            //Session.Contents.Remove("AllGroupID");
            string[] words = a.Split(',');

            string queryCus = "";

         
            for (int i = 0; i < (words.Length-1); i++)
            {
                if (i == 0)
                {
                    if (CheckCus(words[i]) == 0)
                    {
                        queryCus += " AND Cus_Id = " + words[i]; 
                    }
                }
                else {
                    if (CheckCus(words[i]) == 0)
                    {
                        queryCus += " OR Cus_Id = " + words[i];
                        
                    }
                }
            }
             try
            {
                cnnString = ConfigurationManager.ConnectionStrings["DATNconnection"].ToString();
                cnn = new SqlConnection(cnnString);
                cnn.Open();

                string SearchQuery = "SELECT * FROM dbo.Customers WHERE 1 = 1"
                                     + queryCus;

                //Response.Write(SearchQuery);
                SqlCommand cmd = new SqlCommand(SearchQuery, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                DataView dv;
                dv = new DataView(ds.Tables[0]);

                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //btnDelete.Visible = true;
                        gvCheckboxes.DataSource = dv;
                        gvCheckboxes.DataBind();
                    }
                    else
                    {
                        lb_AddtoGroup.Text = "Không có dữ liệu về bảng này!";
                    }
                }
                else
                {
                    lb_AddtoGroup.Text = "Không kết nối được đến database.";
                }
            }
            catch (Exception ex)
            {
                cnn.Close();
                Response.Write("Lỗi hiển thị gridview: " + ex);
            }
            finally
            {
                cnn.Close();
            }
        }

        //Click để thêm Khách Hàng vào Nhóm:
        protected void btnAddtoGroup_Click(object sender, EventArgs e)
        {
            if (Drr_Group.SelectedValue != "-1")
            {
                //Tạo vào nhóm GroupCS
                AddtoGroupCs();
            }
            else {
                lb_AddtoGroup.Text = "Bạn Chưa Chọn Nhóm khách Hàng!";
            }
        }

        protected void btCreatGroup_Click(object sender, EventArgs e)
        {
            Response.Redirect("../GroupCustomer/CreateGroup.aspx");
        }

      
    }
}